{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-01-08T16:26:25.482148Z",
     "start_time": "2025-01-08T16:26:25.047958Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ],
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "merge() 是用于合并两个 DataFrame 的函数。它类似于 SQL 中的 JOIN 操作，可以根据一个或多个键（key）将两个数据集合并在一起。",
   "id": "48632bba0ad38fd4"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "相关参数：\n",
    "\n",
    "left：左侧的 DataFrame。\n",
    "\n",
    "right：右侧的 DataFrame。\n",
    "\n",
    "how：合并方式，默认为 'inner'。可选值包括：\n",
    "\n",
    "'inner'：内连接，只保留键匹配的行。\n",
    "\n",
    "'left'：左连接，保留左侧 DataFrame 的所有行。\n",
    "\n",
    "'right'：右连接，保留右侧 DataFrame 的所有行。\n",
    "\n",
    "'outer'：外连接，保留所有行，缺失值用 NaN 填充。\n",
    "\n",
    "on：用于连接的列名（两个 DataFrame 中列名相同）。\n",
    "\n",
    "left_on：左侧 DataFrame 中用于连接的列名。\n",
    "\n",
    "right_on：右侧 DataFrame 中用于连接的列名。\n",
    "\n",
    "left_index：是否使用左侧 DataFrame 的索引作为连接键。\n",
    "\n",
    "right_index：是否使用右侧 DataFrame 的索引作为连接键。\n",
    "\n",
    "suffixes：当两个 DataFrame 有相同列名时，用于区分列名的后缀。"
   ],
   "id": "5970fbb68e29218a"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:26:25.491951Z",
     "start_time": "2025-01-08T16:26:25.483147Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 创建两个 DataFrame\n",
    "df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],'data1' : np.random.randint(0,10,7)})\n",
    "df_obj2 = pd.DataFrame({'key': ['a', 'b', 'd'],'data2' : np.random.randint(0,10,3)})\n",
    "print(df_obj1)\n",
    "print(df_obj2)"
   ],
   "id": "56553a30e79c2c6a",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1\n",
      "0   b      3\n",
      "1   b      8\n",
      "2   a      2\n",
      "3   c      0\n",
      "4   a      5\n",
      "5   a      5\n",
      "6   b      6\n",
      "  key  data2\n",
      "0   a      0\n",
      "1   b      8\n",
      "2   d      3\n"
     ]
    }
   ],
   "execution_count": 2
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:27:00.247329Z",
     "start_time": "2025-01-08T16:27:00.239361Z"
    }
   },
   "cell_type": "code",
   "source": [
    "#默认将重叠列的列名作为“外键”进行连接\n",
    "print(pd.merge(df_obj1,df_obj2))"
   ],
   "id": "46c80dbd3107b968",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1  data2\n",
      "0   b      3      8\n",
      "1   b      8      8\n",
      "2   a      2      0\n",
      "3   a      5      0\n",
      "4   a      5      0\n",
      "5   b      6      8\n"
     ]
    }
   ],
   "execution_count": 3
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:27:13.922260Z",
     "start_time": "2025-01-08T16:27:13.916892Z"
    }
   },
   "cell_type": "code",
   "source": [
    "#on显示指定“外键”\n",
    "print(pd.merge(df_obj1,df_obj2,on='key'))"
   ],
   "id": "213ca5a924331a6e",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1  data2\n",
      "0   b      3      8\n",
      "1   b      8      8\n",
      "2   a      2      0\n",
      "3   a      5      0\n",
      "4   a      5      0\n",
      "5   b      6      8\n"
     ]
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:27:33.782879Z",
     "start_time": "2025-01-08T16:27:33.776255Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# left_on，right_on分别指定左侧数据和右侧数据的“外键”\n",
    "# 更改列名\n",
    "df_obj1=df_obj1.rename(columns={'key':'key1'})\n",
    "df_obj2=df_obj2.rename(columns={'key':'key2'})\n",
    "print(pd.merge(df_obj1,df_obj2,left_on='key1',right_on='key2'))"
   ],
   "id": "8102c6e1b3b98c57",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    b      3    b      8\n",
      "1    b      8    b      8\n",
      "2    a      2    a      0\n",
      "3    a      5    a      0\n",
      "4    a      5    a      0\n",
      "5    b      6    b      8\n"
     ]
    }
   ],
   "execution_count": 5
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:28:02.165099Z",
     "start_time": "2025-01-08T16:28:02.158599Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# “外连接”\n",
    "print(pd.merge(df_obj1,df_obj2,left_on='key1',right_on='key2',how='outer'))"
   ],
   "id": "bfc5e6bb9d625a92",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    a    2.0    a    0.0\n",
      "1    a    5.0    a    0.0\n",
      "2    a    5.0    a    0.0\n",
      "3    b    3.0    b    8.0\n",
      "4    b    8.0    b    8.0\n",
      "5    b    6.0    b    8.0\n",
      "6    c    0.0  NaN    NaN\n",
      "7  NaN    NaN    d    3.0\n"
     ]
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:28:24.100774Z",
     "start_time": "2025-01-08T16:28:24.094487Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 左连接\n",
    "print(pd.merge(df_obj1,df_obj2,left_on='key1',right_on='key2',how='left'))"
   ],
   "id": "362e8f6faf5e0775",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    b      3    b    8.0\n",
      "1    b      8    b    8.0\n",
      "2    a      2    a    0.0\n",
      "3    c      0  NaN    NaN\n",
      "4    a      5    a    0.0\n",
      "5    a      5    a    0.0\n",
      "6    b      6    b    8.0\n"
     ]
    }
   ],
   "execution_count": 7
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:28:39.274162Z",
     "start_time": "2025-01-08T16:28:39.268080Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 右连接\n",
    "print(pd.merge(df_obj1,df_obj2,left_on='key1',right_on='key2',how='right'))"
   ],
   "id": "cefe804b4f754275",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1  data1 key2  data2\n",
      "0    a    2.0    a      0\n",
      "1    a    5.0    a      0\n",
      "2    a    5.0    a      0\n",
      "3    b    3.0    b      8\n",
      "4    b    8.0    b      8\n",
      "5    b    6.0    b      8\n",
      "6  NaN    NaN    d      3\n"
     ]
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:29:33.329586Z",
     "start_time": "2025-01-08T16:29:33.322913Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 处理重复列名\n",
    "df_obj1=pd.DataFrame({'key':['b','b','a','c','a','a','b'],'data':np.random.randint(0,10,7)})\n",
    "df_obj2=pd.DataFrame({'key':['a','b','d'],'data':np.random.randint(0,10,3)})\n",
    "print(pd.merge(df_obj1,df_obj2,on='key',suffixes=('_left','_right')))"
   ],
   "id": "c4b287a0465d9e0e",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data_left  data_right\n",
      "0   b          3           2\n",
      "1   b          7           2\n",
      "2   a          4           7\n",
      "3   a          2           7\n",
      "4   a          8           7\n",
      "5   b          0           2\n"
     ]
    }
   ],
   "execution_count": 9
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:30:03.917630Z",
     "start_time": "2025-01-08T16:30:03.910844Z"
    }
   },
   "cell_type": "code",
   "source": [
    " # 按索引连接\n",
    "df_obj1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],'data1' : np.random.randint(0,10,7)})\n",
    "df_obj2 = pd.DataFrame({'data2' : np.random.randint(0,10,3)}, index=['a', 'b', 'd'])\n",
    "print(pd.merge(df_obj1, df_obj2, left_on='key', right_index=True))"
   ],
   "id": "e149a31d52ae6fee",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1  data2\n",
      "0   b      6      7\n",
      "1   b      0      7\n",
      "2   a      8      2\n",
      "4   a      0      2\n",
      "5   a      9      2\n",
      "6   b      0      7\n"
     ]
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-08T16:30:19.546542Z",
     "start_time": "2025-01-08T16:30:19.540501Z"
    }
   },
   "cell_type": "code",
   "source": "print(pd.merge(df_obj1, df_obj2, left_on = \"key\", right_index = True, how = \"outer\"))#指定合并方式为外连接，即保留所有行，缺失值用 NaN 填充",
   "id": "45f346a818770e74",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    key  data1  data2\n",
      "2.0   a    8.0    2.0\n",
      "4.0   a    0.0    2.0\n",
      "5.0   a    9.0    2.0\n",
      "0.0   b    6.0    7.0\n",
      "1.0   b    0.0    7.0\n",
      "6.0   b    0.0    7.0\n",
      "3.0   c    1.0    NaN\n",
      "NaN   d    NaN    9.0\n"
     ]
    }
   ],
   "execution_count": 11
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
